ajDBRunStoredProc function

Available since AlchemyJ v4.1

Description

The ajDBRunStoredProc function executes a stored procedure and returns the result. This function can support MySQl, MariaDB, MSSQL, PostgreSql and Oracle database. Please take note that to run this function in Excel, you would need to set up the Data Source Connection in ##ExternalResources.

Syntax

ajDBRunStoredProc(stored_procedure_name, [parameter], [return_type], [transpose], [return_header], [data_source_id], [convert_to_text], [run_condition], [run_by_function_point_only])

Argument Name	Argument Type	Description
stored_procedure_name (required)	Range / Array	The name of the stored procedure to execute.
parameter (optional)	Double	The range of cells to form the list of input and output parameters. Refer to the SQLParameters Snippet to see how to use this snippet to define the required fields.
return_type (optional)	Double	0 indicates the function will return a set of records (in cell array). 1 indicates the function will return the output parameters. 2 indicates to return the number of the affected records, or the number of records when stored procedure with SQL select statement. The default value is 0.
transpose (optional)	Boolean	If it equals TRUE, the return result will be transposed. This only affects the return that has multiple cells. The default value is FALSE.
return_header (optional)	Boolean	If it equals TRUE, the return result will include the column headers as the first row. This only applies when ‘return_type’ is 0 or 1. If it equals FALSE, the return result will contain data only. The default value is FALSE.
data_source_id (optional)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
convert_to_text (optional)	Boolean	If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1. Return Value: Database operations results or the count
2. Return Type: Single Value / Multiple values (array formula)

Example

Before using ajDBRunStoredProc, the database connection must be set up in the Data Source Connection in ##ExternalResources worksheet.

Before running this function point, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.

Examples use the following data.

Example 1 - Return Type is 0

A stored procedure returns records, the stored procedure as below.

DELIMITER $$

CREATE PROCEDURE `SP_PROC2`(cname varchar(50), clevel int)
BEGIN
    SELECT CUSTOMER_NAME,GENDER,CUSTOMER_LVL FROM tb_customer WHERE CUSTOMER_NAME = cname OR CUSTOMER_LVL > clevel;
END $$

DELIMITER ;

Click AlchemyJ ribbon Insert Snippet and select SQL Parameters . A table will be inserted.

In SQL Parameters table, the values are as below. The return_type is 0 since it returns records, and the return_header is TRUE, therefore the returned record with column name. the output parameter.

Notice: The parameter sequence must match the parameter sequence of stored procedure.

Example 2 - Return Type is 1

A stored procedure returns output parameter whose direction is inout or out, the stored procedure as below.

DELIMITER $$

CREATE PROCEDURE `SP_PROC3`(inout cname varchar(50), in new_level int, out output_clevel int)
BEGIN
    UPDATE tb_customer SET CUSTOMER_LVL = new_level WHERE CUSTOMER_NAME = cname;
    SET output_clevel = new_level;
    SET cname = concat(cname,'_U');
END $$

DELIMITER ;

In SQL Parameters table, the values are as below. The return_type is 1, it returns the values of output parameter.

If the stored procedure sets a default value for parameter,you can use an empty input for it. For example, new_level is set a default value in stored procedure , then the parameter new_level should be kept.

Example 3 - Return Type is 2

The result is number of record which is selected from database, and the stored procedure is as same as example1 .

Example 4 - Parameters use separators

The separator symbols is configured in %%AppConfig worksheet.

The value of parameter is as below, use the value of parameter-separator to separator parameter name, and use the value of parameter-type-separator to separator parameter name,value,type and direction.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Invalid stored procedure name.
Invalid return type, it must be 0, 1, or 2.
Return header is TRUE, but return type is not 0 or 1.
DB connection error.